# import sys
# import time
# import json
import os
from datetime import datetime
# from mongoengine import *
import csv
import xlrd
import xlwt
import openpyxl
from openpyxl import Workbook,load_workbook
from openpyxl.worksheet.copier import WorksheetCopy
from openpyxl.styles import Font as excelFont
from exportPolt import *
from openpyxl.styles import PatternFill, Border, Side, Font, Alignment
# import win32com.client as win32

# def xls_to_xlsx(filedir):
#     excel = win32.gencache.EnsureDispatch('Excel.Application')
#     wb = excel.Workbooks.Open('D:\work\yibide\in\【生意参谋平台】商品_所有终端_2021-02-15_2021-02-21.xls')
#     # xlsx:FileFormat=51 xls:FileFormat=56 后缀区分大小写
#     # wb.SaveAs(filedir.replace('xls','xlsx'),FileFormat=51)
#     wb.SaveAs('D:\work\yibide\in\【生意参谋平台】商品_所有终端_2021-02-15_2021-02-21.xlsx')
#     wb.Close()
#     excel.Application.Quit()

daily_file = './in/太阳谷Tmall项目日报&竞品.xlsx'
weekly_file = ''
daily_file_out = './export/太阳谷Tmall项目日报&竞品.xlsx'
weekly_file_out = './export/太阳谷周报及计划更新.xlsx'


for root, dirs, files in os.walk("./stencil"):
    for file in files:
        if "周报" in os.path.splitext(file)[0]:    
            weekly_file = (root + "/" + file)

def read_export_file():
    global dailywb
    global weeklywb
    # dailywb = load_workbook(daily_file)
    # dailywb = xlwt.open_workbook(daily_file)
    weeklywb = load_workbook(weekly_file)
    # weeklywb = xlwt.open_workbook(weekly_file)

def add_daily_data(bookhongguan):
    # dailywb['日报'].iter_cols()
    dailydata = tuple(dailywb['日报'])

    bookhongguansheet = bookhongguan.sheets()[0]
    row =bookhongguansheet.nrows#总行数
    importdata = bookhongguansheet

    for index,i in enumerate(range(row)):
        if index>5:
            data = importdata.row_values(i)
            # print(formatdate(data))
            for indexa,e in enumerate(dailydata[1]):
                if e.value != None and indexa>4:
                    if formatdate(data[0]) == e.value:
                        # print(importdata.row_values(index))
                        dailywb['日报'].cell(3,indexa+1).value = data[2]#日业绩
                        # dailywb['日报'].cell(4,indexa+1).value = data[2]#泰森
                        # dailywb['日报'].cell(5,indexa+1).value = data[2]#圣农
                        # dailywb['日报'].cell(6,indexa+1).value = data[2]#日访客
                        dailywb['日报'].cell(7,indexa+1).value = data[21]#日转化
                        dailywb['日报'].cell(8,indexa+1).value = data[13]#日买家
                        dailywb['日报'].cell(9,indexa+1).value = data[26]#日客单
    # print(dailydata[1])
    # print(bookhongguan.sheet_by_name(bookhongguan.sheet_names()[0]).row_values(6))

def add_jingpin(bookjingpin):
    dailydata = tuple(dailywb['日报'])
    bookjingpinsheet = bookjingpin.sheets()[0]
    row =bookjingpinsheet.nrows#总行数
    importdata = bookjingpinsheet
    for index,i in enumerate(range(row)):
        if index >0:
            data = importdata.row_values(i)
            #name data[0] date data[2] price data[3] count data[4]
            for indexa,e in enumerate(dailydata[1]):
                if e.value != None and indexa>4:
                    if formatdate(data[2]) == e.value:
                        if data[0] == "太阳谷旗舰店":
                            dailywb['日报'].cell(6,indexa+1).value = data[4]#日访客
                        elif data[0] == "Tyson泰森食品官方旗舰店":
                            dailywb['日报'].cell(4,indexa+1).value = data[3]#店1
                        elif data[0] == "圣农旗舰店":
                            dailywb['日报'].cell(4,indexa+1).value = data[3]#店2

def add_zhengti(zhengticsv):
    zhengtidict = []
    for index,i in enumerate(zhengticsv):
        zhengtidict.append(i)

    #本周
    weeklywb['上周回顾'].cell(4,2).value = format(float(zhengtidict[1][1]),',')#支付金额
    weeklywb['上周回顾'].cell(4,3).value = format(int(zhengtidict[1][9]),',')#访客数
    weeklywb['上周回顾'].cell(4,4).value = zhengtidict[1][2]#支付转化率
    weeklywb['上周回顾'].cell(4,5).value = format(float(zhengtidict[1][3]),',')#客单价
    weeklywb['上周回顾'].cell(4,6).value = format(float(zhengtidict[1][18]),',')#成功退款金额
    weeklywb['上周回顾'].cell(4,7).value = format(int(zhengtidict[1][4]),',')#支付买家数
    weeklywb['上周回顾'].cell(4,8).value = format(int(zhengtidict[1][5]),',')#支付老买家数
    weeklywb['上周回顾'].cell(4,9).value = format(int(zhengtidict[1][8]),',')#支付子订单数
    weeklywb['上周回顾'].cell(4,10).value = format(int(zhengtidict[1][7]),',')#支付件数
    weeklywb['上周回顾'].cell(4,11).value = format(int(zhengtidict[1][12]),',')#加购件数
    weeklywb['上周回顾'].cell(4,12).value = format(int(zhengtidict[1][13]),',')#商品收藏人数
    weeklywb['上周回顾'].cell(4,13).value = format(float(zhengtidict[1][14]),',')#支直通车消耗
    weeklywb['上周回顾'].cell(4,14).value = format(float(zhengtidict[1][15]),',')#超级推荐消耗
    weeklywb['上周回顾'].cell(4,15).value = format(float(zhengtidict[1][17]),',')#淘宝客佣金

    #环比上周
    weeklywb['上周回顾'].cell(5,2).value = zhengtidict[14][1]#支付金额
    weeklywb['上周回顾'].cell(5,2).font = excelFont(color=returncolor(zhengtidict[14][1]))
    weeklywb['上周回顾'].cell(5,3).value = zhengtidict[14][9]#访客数
    weeklywb['上周回顾'].cell(5,3).font = excelFont(color=returncolor(zhengtidict[14][9]))
    weeklywb['上周回顾'].cell(5,4).value = zhengtidict[14][2]#支付转化率
    weeklywb['上周回顾'].cell(5,4).font = excelFont(color=returncolor(zhengtidict[14][2]))
    weeklywb['上周回顾'].cell(5,5).value = zhengtidict[14][3]#客单价
    weeklywb['上周回顾'].cell(5,5).font = excelFont(color=returncolor(zhengtidict[14][3]))
    weeklywb['上周回顾'].cell(5,6).value = zhengtidict[14][18]#成功退款金额
    weeklywb['上周回顾'].cell(5,6).font = excelFont(color=returncolor(zhengtidict[14][18]))
    weeklywb['上周回顾'].cell(5,7).value = zhengtidict[14][4]#支付买家数
    weeklywb['上周回顾'].cell(5,7).font = excelFont(color=returncolor(zhengtidict[14][4]))
    weeklywb['上周回顾'].cell(5,8).value = zhengtidict[14][5]#支付老买家数
    weeklywb['上周回顾'].cell(5,8).font = excelFont(color=returncolor(zhengtidict[14][5]))
    weeklywb['上周回顾'].cell(5,9).value = zhengtidict[14][8]#支付子订单数
    weeklywb['上周回顾'].cell(5,9).font = excelFont(color=returncolor(zhengtidict[14][8]))
    weeklywb['上周回顾'].cell(5,10).value = zhengtidict[14][7]#支付件数
    weeklywb['上周回顾'].cell(5,10).font = excelFont(color=returncolor(zhengtidict[14][7]))
    weeklywb['上周回顾'].cell(5,11).value = zhengtidict[14][12]#加购件数
    weeklywb['上周回顾'].cell(5,11).font = excelFont(color=returncolor(zhengtidict[14][12]))
    weeklywb['上周回顾'].cell(5,12).value = zhengtidict[14][13]#商品收藏人数
    weeklywb['上周回顾'].cell(5,12).font = excelFont(color=returncolor(zhengtidict[14][13]))
    weeklywb['上周回顾'].cell(5,13).value = zhengtidict[14][14]#支直通车消耗
    weeklywb['上周回顾'].cell(5,13).font = excelFont(color=returncolor(zhengtidict[14][14]))
    weeklywb['上周回顾'].cell(5,14).value = zhengtidict[14][15]#超级推荐消耗
    weeklywb['上周回顾'].cell(5,14).font = excelFont(color=returncolor(zhengtidict[14][15]))
    weeklywb['上周回顾'].cell(5,15).value = zhengtidict[14][17]#淘宝客佣金
    weeklywb['上周回顾'].cell(5,15).font = excelFont(color=returncolor(zhengtidict[14][17]))

def add_yunying(bookyunying):
    bookyunyingsheet = bookyunying.sheets()[0]
    row =bookyunyingsheet.nrows#总行数
    importdata = bookyunyingsheet
    tempdata = []
    for index,i in enumerate(range(row)):
        tempdata.append(importdata.row_values(i))
    weeklywb['上周回顾'].cell(6,2).value = formatyunyingdata(tempdata[1][2])[0]#支付金额
    weeklywb['上周回顾'].cell(6,2).font = excelFont(color=formatyunyingdata(tempdata[1][2])[1])
    weeklywb['上周回顾'].cell(6,3).value = formatyunyingdata(tempdata[9][2])[0]#访客数
    weeklywb['上周回顾'].cell(6,3).font = excelFont(color=formatyunyingdata(tempdata[9][2])[1])
    weeklywb['上周回顾'].cell(6,4).value = formatyunyingdata(tempdata[2][2])[0]#支付转化率
    weeklywb['上周回顾'].cell(6,4).font = excelFont(color=formatyunyingdata(tempdata[2][2])[1])
    weeklywb['上周回顾'].cell(6,5).value = formatyunyingdata(tempdata[3][2])[0]#客单价
    weeklywb['上周回顾'].cell(6,5).font = excelFont(color=formatyunyingdata(tempdata[3][2])[1])
    weeklywb['上周回顾'].cell(6,6).value = formatyunyingdata(tempdata[18][2])[0]#成功退款金额
    weeklywb['上周回顾'].cell(6,6).font = excelFont(color=formatyunyingdata(tempdata[18][2])[1])
    weeklywb['上周回顾'].cell(6,7).value = formatyunyingdata(tempdata[4][2])[0]#支付买家数
    weeklywb['上周回顾'].cell(6,7).font = excelFont(color=formatyunyingdata(tempdata[4][2])[1])
    weeklywb['上周回顾'].cell(6,8).value = formatyunyingdata(tempdata[5][2])[0]#支付老买家数
    weeklywb['上周回顾'].cell(6,8).font = excelFont(color=formatyunyingdata(tempdata[5][2])[1])
    weeklywb['上周回顾'].cell(6,9).value = formatyunyingdata(tempdata[8][2])[0]#支付子订单数
    weeklywb['上周回顾'].cell(6,9).font = excelFont(color=formatyunyingdata(tempdata[8][2])[1])
    weeklywb['上周回顾'].cell(6,10).value = formatyunyingdata(tempdata[7][2])[0]#支付件数
    weeklywb['上周回顾'].cell(6,10).font = excelFont(color=formatyunyingdata(tempdata[7][2])[1])
    weeklywb['上周回顾'].cell(6,11).value = formatyunyingdata(tempdata[12][2])[0]#加购件数
    weeklywb['上周回顾'].cell(6,11).font = excelFont(color=formatyunyingdata(tempdata[12][2])[1])
    weeklywb['上周回顾'].cell(6,12).value = formatyunyingdata(tempdata[13][2])[0]#商品收藏人数
    weeklywb['上周回顾'].cell(6,12).font = excelFont(color=formatyunyingdata(tempdata[13][2])[1])
    weeklywb['上周回顾'].cell(6,13).value = formatyunyingdata(tempdata[14][2])[0]#支直通车消耗
    weeklywb['上周回顾'].cell(6,13).font = excelFont(color=formatyunyingdata(tempdata[14][2])[1])
    weeklywb['上周回顾'].cell(6,14).value = formatyunyingdata(tempdata[15][2])[0]#超级推荐消耗
    weeklywb['上周回顾'].cell(6,14).font = excelFont(color=formatyunyingdata(tempdata[15][2])[1])
    weeklywb['上周回顾'].cell(6,15).value = formatyunyingdata(tempdata[17][2])[0]#淘宝客佣金
    weeklywb['上周回顾'].cell(6,15).font = excelFont(color=formatyunyingdata(tempdata[17][2])[1])


def add_wuxian(bookwuxian):
    bookwuxiansheet = bookwuxian.sheets()[0]
    row =bookwuxiansheet.nrows#总行数
    importdata = bookwuxiansheet
    tempdata = []
    for index,i in enumerate(range(row)):
        tempdata.append(importdata.row_values(i))
    for index,item in enumerate(tempdata):
        if item[0]=="淘内免费" and item[1] == "汇总":
            weeklywb['上周回顾'].cell(12+2,2).value = item[2]#访客数
            weeklywb['上周回顾'].cell(13+2,2).value = item[3]
            weeklywb['上周回顾'].cell(13+2,2).font = excelFont(color=returncolor(item[3]))
            weeklywb['上周回顾'].cell(12+2,3).value = item[10]#支付金额
            weeklywb['上周回顾'].cell(13+2,3).value = item[11]
            weeklywb['上周回顾'].cell(13+2,3).font = excelFont(color=returncolor(item[11]))
            weeklywb['上周回顾'].cell(12+2,4).value = item[12]#支付买家数
            weeklywb['上周回顾'].cell(13+2,4).value = item[13]
            weeklywb['上周回顾'].cell(13+2,4).font = excelFont(color=returncolor(item[13]))
            weeklywb['上周回顾'].cell(12+2,5).value = item[14]#支付转化率
            weeklywb['上周回顾'].cell(13+2,5).value = item[15]
            weeklywb['上周回顾'].cell(13+2,5).font = excelFont(color=returncolor(item[15]))
            weeklywb['上周回顾'].cell(12+2,6).value = item[16]#客单价
            weeklywb['上周回顾'].cell(13+2,6).value = item[17]
            weeklywb['上周回顾'].cell(13+2,6).font = excelFont(color=returncolor(item[17]))
            # weeklywb['上周回顾'].cell(12+2,7).value = item[2]#roi
            # weeklywb['上周回顾'].cell(13+2,7).value = item[3]
        if item[0]=="淘内免费" and item[1] == "手淘淘宝直播":
            weeklywb['上周回顾'].cell(14+2,2).value = item[2]#访客数
            weeklywb['上周回顾'].cell(15+2,2).value = item[3]
            weeklywb['上周回顾'].cell(15+2,2).font = excelFont(color=returncolor(item[3]))
            weeklywb['上周回顾'].cell(14+2,3).value = item[10]#支付金额
            weeklywb['上周回顾'].cell(15+2,3).value = item[11]
            weeklywb['上周回顾'].cell(15+2,3).font = excelFont(color=returncolor(item[11]))
            weeklywb['上周回顾'].cell(14+2,4).value = item[12]#支付买家数
            weeklywb['上周回顾'].cell(15+2,4).value = item[13]
            weeklywb['上周回顾'].cell(15+2,4).font = excelFont(color=returncolor(item[13]))
            weeklywb['上周回顾'].cell(14+2,5).value = item[14]#支付转化率
            weeklywb['上周回顾'].cell(15+2,5).value = item[15]
            weeklywb['上周回顾'].cell(15+2,5).font = excelFont(color=returncolor(item[15]))
            weeklywb['上周回顾'].cell(14+2,6).value = item[16]#客单价
            weeklywb['上周回顾'].cell(15+2,6).value = item[17]
            weeklywb['上周回顾'].cell(15+2,6).font = excelFont(color=returncolor(item[17]))
            # weeklywb['上周回顾'].cell(14+2,7).value = item[2]#roi
            # weeklywb['上周回顾'].cell(15+2,7).value = item[3]
        if item[0]=="淘内免费" and item[1] == "手淘搜索":
            weeklywb['上周回顾'].cell(16+2,2).value = item[2]#访客数
            weeklywb['上周回顾'].cell(17+2,2).value = item[3]
            weeklywb['上周回顾'].cell(17+2,2).font = excelFont(color=returncolor(item[3]))
            weeklywb['上周回顾'].cell(16+2,3).value = item[10]#支付金额
            weeklywb['上周回顾'].cell(17+2,3).value = item[11]
            weeklywb['上周回顾'].cell(17+2,3).font = excelFont(color=returncolor(item[11]))
            weeklywb['上周回顾'].cell(16+2,4).value = item[12]#支付买家数
            weeklywb['上周回顾'].cell(17+2,4).value = item[13]
            weeklywb['上周回顾'].cell(17+2,4).font = excelFont(color=returncolor(item[13]))
            weeklywb['上周回顾'].cell(16+2,5).value = item[14]#支付转化率
            weeklywb['上周回顾'].cell(17+2,5).value = item[15]
            weeklywb['上周回顾'].cell(17+2,5).font = excelFont(color=returncolor(item[15]))
            weeklywb['上周回顾'].cell(16+2,6).value = item[16]#客单价
            weeklywb['上周回顾'].cell(17+2,6).value = item[17]
            weeklywb['上周回顾'].cell(17+2,6).font = excelFont(color=returncolor(item[17]))
            # weeklywb['上周回顾'].cell(16+2,7).value = item[2]#roi
            # weeklywb['上周回顾'].cell(17+2,7).value = item[3]
        if item[0]=="淘内免费" and item[1] == "聚划算百亿补贴":
            weeklywb['上周回顾'].cell(18+2,2).value = item[2]#访客数
            weeklywb['上周回顾'].cell(19+2,2).value = item[3]
            weeklywb['上周回顾'].cell(19+2,2).font = excelFont(color=returncolor(item[3]))
            weeklywb['上周回顾'].cell(18+2,3).value = item[10]#支付金额
            weeklywb['上周回顾'].cell(19+2,3).value = item[11]
            weeklywb['上周回顾'].cell(19+2,3).font = excelFont(color=returncolor(item[11]))
            weeklywb['上周回顾'].cell(18+2,4).value = item[12]#支付买家数
            weeklywb['上周回顾'].cell(19+2,4).value = item[13]
            weeklywb['上周回顾'].cell(19+2,4).font = excelFont(color=returncolor(item[13]))
            weeklywb['上周回顾'].cell(18+2,5).value = item[14]#支付转化率
            weeklywb['上周回顾'].cell(19+2,5).value = item[15]
            weeklywb['上周回顾'].cell(19+2,5).font = excelFont(color=returncolor(item[15]))
            weeklywb['上周回顾'].cell(18+2,6).value = item[16]#客单价
            weeklywb['上周回顾'].cell(19+2,6).value = item[17]
            weeklywb['上周回顾'].cell(19+2,6).font = excelFont(color=returncolor(item[17]))
            # weeklywb['上周回顾'].cell(18+2,7).value = item[2]#roi
            # weeklywb['上周回顾'].cell(19+2,7).value = item[3]
        if item[0]=="淘内免费" and item[1] == "淘内免费其他":
            weeklywb['上周回顾'].cell(20+2,2).value = item[2]#访客数
            weeklywb['上周回顾'].cell(21+2,2).value = item[3]
            weeklywb['上周回顾'].cell(21+2,2).font = excelFont(color=returncolor(item[3]))
            weeklywb['上周回顾'].cell(20+2,3).value = item[10]#支付金额
            weeklywb['上周回顾'].cell(21+2,3).value = item[11]
            weeklywb['上周回顾'].cell(21+2,3).font = excelFont(color=returncolor(item[11]))
            weeklywb['上周回顾'].cell(20+2,4).value = item[12]#支付买家数
            weeklywb['上周回顾'].cell(21+2,4).value = item[13]
            weeklywb['上周回顾'].cell(21+2,4).font = excelFont(color=returncolor(item[13]))
            weeklywb['上周回顾'].cell(20+2,5).value = item[14]#支付转化率
            weeklywb['上周回顾'].cell(21+2,5).value = item[15]
            weeklywb['上周回顾'].cell(21+2,5).font = excelFont(color=returncolor(item[15]))
            weeklywb['上周回顾'].cell(20+2,6).value = item[16]#客单价
            weeklywb['上周回顾'].cell(21+2,6).value = item[17]
            weeklywb['上周回顾'].cell(21+2,6).font = excelFont(color=returncolor(item[17]))
            # weeklywb['上周回顾'].cell(20+2,7).value = item[2]#roi
            # weeklywb['上周回顾'].cell(21+2,7).value = item[3]
        if item[0]=="淘内免费" and item[1] == "手淘淘金币":
            weeklywb['上周回顾'].cell(22+2,2).value = item[2]#访客数
            weeklywb['上周回顾'].cell(23+2,2).value = item[3]
            weeklywb['上周回顾'].cell(23+2,2).font = excelFont(color=returncolor(item[3]))
            weeklywb['上周回顾'].cell(22+2,3).value = item[10]#支付金额
            weeklywb['上周回顾'].cell(23+2,3).value = item[11]
            weeklywb['上周回顾'].cell(23+2,3).font = excelFont(color=returncolor(item[11]))
            weeklywb['上周回顾'].cell(22+2,4).value = item[12]#支付买家数
            weeklywb['上周回顾'].cell(23+2,4).value = item[13]
            weeklywb['上周回顾'].cell(23+2,4).font = excelFont(color=returncolor(item[13]))
            weeklywb['上周回顾'].cell(22+2,5).value = item[14]#支付转化率
            weeklywb['上周回顾'].cell(23+2,5).value = item[15]
            weeklywb['上周回顾'].cell(23+2,5).font = excelFont(color=returncolor(item[15]))
            weeklywb['上周回顾'].cell(22+2,6).value = item[16]#客单价
            weeklywb['上周回顾'].cell(23+2,6).value = item[17]
            weeklywb['上周回顾'].cell(23+2,6).font = excelFont(color=returncolor(item[17]))
            # weeklywb['上周回顾'].cell(22+2,7).value = item[2]#roi
            # weeklywb['上周回顾'].cell(23+2,7).value = item[3]
        if item[0]=="淘内免费" and item[1] == "手淘推荐":
            weeklywb['上周回顾'].cell(24+2,2).value = item[2]#访客数
            weeklywb['上周回顾'].cell(25+2,2).value = item[3]
            weeklywb['上周回顾'].cell(25+2,2).font = excelFont(color=returncolor(item[3]))
            weeklywb['上周回顾'].cell(24+2,3).value = item[10]#支付金额
            weeklywb['上周回顾'].cell(25+2,3).value = item[11]
            weeklywb['上周回顾'].cell(25+2,4).font = excelFont(color=returncolor(item[11]))
            weeklywb['上周回顾'].cell(24+2,4).value = item[12]#支付买家数
            weeklywb['上周回顾'].cell(25+2,4).value = item[13]
            weeklywb['上周回顾'].cell(25+2,4).font = excelFont(color=returncolor(item[13]))
            weeklywb['上周回顾'].cell(24+2,5).value = item[14]#支付转化率
            weeklywb['上周回顾'].cell(25+2,5).value = item[15]
            weeklywb['上周回顾'].cell(25+2,5).font = excelFont(color=returncolor(item[15]))
            weeklywb['上周回顾'].cell(24+2,6).value = item[16]#客单价
            weeklywb['上周回顾'].cell(25+2,6).value = item[17]
            weeklywb['上周回顾'].cell(25+2,6).font = excelFont(color=returncolor(item[17]))
            # weeklywb['上周回顾'].cell(24+2,7).value = item[2]#roi
            # weeklywb['上周回顾'].cell(25+2,7).value = item[3]
        if item[0]=="淘内免费" and item[1] == "手淘问大家":
            weeklywb['上周回顾'].cell(26+2,2).value = item[2]#访客数
            weeklywb['上周回顾'].cell(27+2,2).value = item[3]
            weeklywb['上周回顾'].cell(27+2,2).font = excelFont(color=returncolor(item[3]))
            weeklywb['上周回顾'].cell(26+2,3).value = item[10]#支付金额
            weeklywb['上周回顾'].cell(27+2,3).value = item[11]
            weeklywb['上周回顾'].cell(27+2,3).font = excelFont(color=returncolor(item[11]))
            weeklywb['上周回顾'].cell(26+2,4).value = item[12]#支付买家数
            weeklywb['上周回顾'].cell(27+2,4).value = item[13]
            weeklywb['上周回顾'].cell(27+2,4).font = excelFont(color=returncolor(item[13]))
            weeklywb['上周回顾'].cell(26+2,5).value = item[14]#支付转化率
            weeklywb['上周回顾'].cell(27+2,5).value = item[15]
            weeklywb['上周回顾'].cell(27+2,5).font = excelFont(color=returncolor(item[15]))
            weeklywb['上周回顾'].cell(26+2,6).value = item[16]#客单价
            weeklywb['上周回顾'].cell(27+2,6).value = item[17]
            weeklywb['上周回顾'].cell(27+2,6).font = excelFont(color=returncolor(item[17]))
            # weeklywb['上周回顾'].cell(26+2,7).value = item[2]#roi
            # weeklywb['上周回顾'].cell(27+2,7).value = item[3]
        if item[0]=="淘内免费" and item[1] == "手淘旺信":
            weeklywb['上周回顾'].cell(28+2,2).value = item[2]#访客数
            weeklywb['上周回顾'].cell(29+2,2).value = item[3]
            weeklywb['上周回顾'].cell(29+2,2).font = excelFont(color=returncolor(item[3]))
            weeklywb['上周回顾'].cell(28+2,3).value = item[10]#支付金额
            weeklywb['上周回顾'].cell(29+2,3).value = item[11]
            weeklywb['上周回顾'].cell(29+2,3).font = excelFont(color=returncolor(item[11]))
            weeklywb['上周回顾'].cell(28+2,4).value = item[12]#支付买家数
            weeklywb['上周回顾'].cell(29+2,4).value = item[13]
            weeklywb['上周回顾'].cell(29+2,4).font = excelFont(color=returncolor(item[13]))
            weeklywb['上周回顾'].cell(28+2,5).value = item[14]#支付转化率
            weeklywb['上周回顾'].cell(29+2,5).value = item[15]
            weeklywb['上周回顾'].cell(29+2,5).font = excelFont(color=returncolor(item[15]))
            weeklywb['上周回顾'].cell(28+2,6).value = item[16]#客单价
            weeklywb['上周回顾'].cell(29+2,6).value = item[17]
            weeklywb['上周回顾'].cell(29+2,6).font = excelFont(color=returncolor(item[17]))
            # weeklywb['上周回顾'].cell(28+2,7).value = item[2]#roi
            # weeklywb['上周回顾'].cell(29+2,7).value = item[3]
        if item[0]=="淘内免费" and item[1] == "手淘其他店铺":
            weeklywb['上周回顾'].cell(30+2,2).value = item[2]#访客数
            weeklywb['上周回顾'].cell(31+2,2).value = item[3]
            weeklywb['上周回顾'].cell(31+2,2).font = excelFont(color=returncolor(item[3]))
            weeklywb['上周回顾'].cell(30+2,3).value = item[10]#支付金额
            weeklywb['上周回顾'].cell(31+2,3).value = item[11]
            weeklywb['上周回顾'].cell(31+2,3).font = excelFont(color=returncolor(item[11]))
            weeklywb['上周回顾'].cell(30+2,4).value = item[12]#支付买家数
            weeklywb['上周回顾'].cell(31+2,4).value = item[13]
            weeklywb['上周回顾'].cell(31+2,4).font = excelFont(color=returncolor(item[13]))
            weeklywb['上周回顾'].cell(30+2,5).value = item[14]#支付转化率
            weeklywb['上周回顾'].cell(31+2,5).value = item[15]
            weeklywb['上周回顾'].cell(31+2,5).font = excelFont(color=returncolor(item[15]))
            weeklywb['上周回顾'].cell(30+2,6).value = item[16]#客单价
            weeklywb['上周回顾'].cell(31+2,6).value = item[17]
            weeklywb['上周回顾'].cell(31+2,6).font = excelFont(color=returncolor(item[17]))
            # weeklywb['上周回顾'].cell(30+2,7).value = item[2]#roi
            # weeklywb['上周回顾'].cell(31+2,7).value = item[3]
        if item[0]=="淘内免费" and item[1] == "手淘其他店铺商品详情":
            weeklywb['上周回顾'].cell(32+2,2).value = item[2]#访客数
            weeklywb['上周回顾'].cell(33+2,2).value = item[3]
            weeklywb['上周回顾'].cell(33+2,2).font = excelFont(color=returncolor(item[3]))
            weeklywb['上周回顾'].cell(32+2,3).value = item[10]#支付金额
            weeklywb['上周回顾'].cell(33+2,3).value = item[11]
            weeklywb['上周回顾'].cell(33+2,3).font = excelFont(color=returncolor(item[11]))
            weeklywb['上周回顾'].cell(32+2,4).value = item[12]#支付买家数
            weeklywb['上周回顾'].cell(33+2,4).value = item[13]
            weeklywb['上周回顾'].cell(33+2,4).font = excelFont(color=returncolor(item[13]))
            weeklywb['上周回顾'].cell(32+2,5).value = item[14]#支付转化率
            weeklywb['上周回顾'].cell(33+2,5).value = item[15]
            weeklywb['上周回顾'].cell(33+2,5).font = excelFont(color=returncolor(item[15]))
            weeklywb['上周回顾'].cell(32+2,6).value = item[16]#客单价
            weeklywb['上周回顾'].cell(33+2,6).value = item[17]
            weeklywb['上周回顾'].cell(33+2,6).font = excelFont(color=returncolor(item[17]))
            # weeklywb['上周回顾'].cell(32+2,7).value = item[2]#roi
            # weeklywb['上周回顾'].cell(33+2,7).value = item[3]
        if item[0]=="淘内免费" and item[1] == "裂变券引流":
            weeklywb['上周回顾'].cell(34+2,2).value = item[2]#访客数
            weeklywb['上周回顾'].cell(35+2,2).value = item[3]
            weeklywb['上周回顾'].cell(35+2,2).font = excelFont(color=returncolor(item[3]))
            weeklywb['上周回顾'].cell(34+2,3).value = item[10]#支付金额
            weeklywb['上周回顾'].cell(35+2,3).value = item[11]
            weeklywb['上周回顾'].cell(35+2,3).font = excelFont(color=returncolor(item[11]))
            weeklywb['上周回顾'].cell(34+2,4).value = item[12]#支付买家数
            weeklywb['上周回顾'].cell(35+2,4).value = item[13]
            weeklywb['上周回顾'].cell(35+2,4).font = excelFont(color=returncolor(item[13]))
            weeklywb['上周回顾'].cell(34+2,5).value = item[14]#支付转化率
            weeklywb['上周回顾'].cell(35+2,5).value = item[15]
            weeklywb['上周回顾'].cell(35+2,5).font = excelFont(color=returncolor(item[15]))
            weeklywb['上周回顾'].cell(34+2,6).value = item[16]#客单价
            weeklywb['上周回顾'].cell(35+2,6).value = item[17]
            weeklywb['上周回顾'].cell(35+2,6).font = excelFont(color=returncolor(item[17]))
            # weeklywb['上周回顾'].cell(34+2,7).value = item[2]#roi
            # weeklywb['上周回顾'].cell(35+2,7).value = item[3]
        if item[0]=="付费流量" and item[1] == "汇总":
            weeklywb['上周回顾'].cell(36+2,2).value = item[2]#访客数
            weeklywb['上周回顾'].cell(37+2,2).value = item[3]
            weeklywb['上周回顾'].cell(37+2,2).font = excelFont(color=returncolor(item[3]))
            weeklywb['上周回顾'].cell(36+2,3).value = item[10]#支付金额
            weeklywb['上周回顾'].cell(37+2,3).value = item[11]
            weeklywb['上周回顾'].cell(37+2,3).font = excelFont(color=returncolor(item[11]))
            weeklywb['上周回顾'].cell(36+2,4).value = item[12]#支付买家数
            weeklywb['上周回顾'].cell(37+2,4).value = item[13]
            weeklywb['上周回顾'].cell(37+2,4).font = excelFont(color=returncolor(item[13]))
            weeklywb['上周回顾'].cell(36+2,5).value = item[14]#支付转化率
            weeklywb['上周回顾'].cell(37+2,5).value = item[15]
            weeklywb['上周回顾'].cell(37+2,5).font = excelFont(color=returncolor(item[15]))
            weeklywb['上周回顾'].cell(36+2,6).value = item[16]#客单价
            weeklywb['上周回顾'].cell(37+2,6).value = item[17]
            weeklywb['上周回顾'].cell(37+2,6).font = excelFont(color=returncolor(item[17]))
            # weeklywb['上周回顾'].cell(36+2,7).value = item[2]#roi
            # weeklywb['上周回顾'].cell(37+2,7).value = item[3]
        if item[0]=="付费流量" and item[1] == "直通车":
            weeklywb['上周回顾'].cell(38+2,2).value = item[2]#访客数
            weeklywb['上周回顾'].cell(39+2,2).value = item[3]
            weeklywb['上周回顾'].cell(39+2,2).font = excelFont(color=returncolor(item[3]))
            weeklywb['上周回顾'].cell(38+2,3).value = item[10]#支付金额
            weeklywb['上周回顾'].cell(39+2,3).value = item[11]
            weeklywb['上周回顾'].cell(39+2,3).font = excelFont(color=returncolor(item[11]))
            weeklywb['上周回顾'].cell(38+2,4).value = item[12]#支付买家数
            weeklywb['上周回顾'].cell(39+2,4).value = item[13]
            weeklywb['上周回顾'].cell(39+2,4).font = excelFont(color=returncolor(item[13]))
            weeklywb['上周回顾'].cell(38+2,5).value = item[14]#支付转化率
            weeklywb['上周回顾'].cell(39+2,5).value = item[15]
            weeklywb['上周回顾'].cell(39+2,5).font = excelFont(color=returncolor(item[15]))
            weeklywb['上周回顾'].cell(38+2,6).value = item[16]#客单价
            weeklywb['上周回顾'].cell(39+2,6).value = item[17]
            weeklywb['上周回顾'].cell(39+2,6).font = excelFont(color=returncolor(item[17]))
            # weeklywb['上周回顾'].cell(38+2,7).value = item[2]#roi
            # weeklywb['上周回顾'].cell(39+2,7).value = item[3]
        if item[0]=="付费流量" and item[1] == "超级推荐":
            weeklywb['上周回顾'].cell(40+2,2).value = item[2]#访客数
            weeklywb['上周回顾'].cell(41+2,2).value = item[3]
            weeklywb['上周回顾'].cell(41+2,2).font = excelFont(color=returncolor(item[3]))
            weeklywb['上周回顾'].cell(40+2,3).value = item[10]#支付金额
            weeklywb['上周回顾'].cell(41+2,3).value = item[11]
            weeklywb['上周回顾'].cell(41+2,3).font = excelFont(color=returncolor(item[11]))
            weeklywb['上周回顾'].cell(40+2,4).value = item[12]#支付买家数
            weeklywb['上周回顾'].cell(41+2,4).value = item[13]
            weeklywb['上周回顾'].cell(41+2,4).font = excelFont(color=returncolor(item[13]))
            weeklywb['上周回顾'].cell(40+2,5).value = item[14]#支付转化率
            weeklywb['上周回顾'].cell(41+2,5).value = item[15]
            weeklywb['上周回顾'].cell(41+2,5).font = excelFont(color=returncolor(item[15]))
            weeklywb['上周回顾'].cell(40+2,6).value = item[16]#客单价
            weeklywb['上周回顾'].cell(41+2,6).value = item[17]
            weeklywb['上周回顾'].cell(41+2,6).font = excelFont(color=returncolor(item[17]))
            # weeklywb['上周回顾'].cell(40+2,7).value = item[2]#roi
            # weeklywb['上周回顾'].cell(41+2,7).value = item[3]
        if item[0]=="付费流量" and item[1] == "淘宝客":
            weeklywb['上周回顾'].cell(42+2,2).value = item[2]#访客数
            weeklywb['上周回顾'].cell(43+2,2).value = item[3]
            weeklywb['上周回顾'].cell(43+2,2).font = excelFont(color=returncolor(item[3]))
            weeklywb['上周回顾'].cell(42+2,3).value = item[10]#支付金额
            weeklywb['上周回顾'].cell(43+2,3).value = item[11]
            weeklywb['上周回顾'].cell(43+2,3).font = excelFont(color=returncolor(item[11]))
            weeklywb['上周回顾'].cell(42+2,4).value = item[12]#支付买家数
            weeklywb['上周回顾'].cell(43+2,4).value = item[13]
            weeklywb['上周回顾'].cell(43+2,4).font = excelFont(color=returncolor(item[13]))
            weeklywb['上周回顾'].cell(42+2,5).value = item[14]#支付转化率
            weeklywb['上周回顾'].cell(43+2,5).value = item[15]
            weeklywb['上周回顾'].cell(43+2,5).font = excelFont(color=returncolor(item[15]))
            weeklywb['上周回顾'].cell(42+2,6).value = item[16]#客单价
            weeklywb['上周回顾'].cell(43+2,6).value = item[17]
            weeklywb['上周回顾'].cell(43+2,6).font = excelFont(color=returncolor(item[17]))
            # weeklywb['上周回顾'].cell(42+2,7).value = item[2]#roi
            # weeklywb['上周回顾'].cell(43+2,7).value = item[3]
        if item[0]=="付费流量" and item[1] == "智钻":
            weeklywb['上周回顾'].cell(44+2,2).value = item[2]#访客数
            weeklywb['上周回顾'].cell(45+2,2).value = item[3]
            weeklywb['上周回顾'].cell(45+2,2).font = excelFont(color=returncolor(item[3]))
            weeklywb['上周回顾'].cell(44+2,3).value = item[10]#支付金额
            weeklywb['上周回顾'].cell(45+2,3).value = item[11]
            weeklywb['上周回顾'].cell(45+2,3).font = excelFont(color=returncolor(item[11]))
            weeklywb['上周回顾'].cell(44+2,4).value = item[12]#支付买家数
            weeklywb['上周回顾'].cell(45+2,4).value = item[13]
            weeklywb['上周回顾'].cell(45+2,4).font = excelFont(color=returncolor(item[13]))
            weeklywb['上周回顾'].cell(44+2,5).value = item[14]#支付转化率
            weeklywb['上周回顾'].cell(45+2,5).value = item[15]
            weeklywb['上周回顾'].cell(45+2,5).font = excelFont(color=returncolor(item[15]))
            weeklywb['上周回顾'].cell(44+2,6).value = item[16]#客单价
            weeklywb['上周回顾'].cell(45+2,6).value = item[17]
            weeklywb['上周回顾'].cell(45+2,6).font = excelFont(color=returncolor(item[17]))
            # weeklywb['上周回顾'].cell(44+2,7).value = item[2]#roi
            # weeklywb['上周回顾'].cell(45+2,7).value = item[3]
        if item[0]=="付费流量" and item[1] == "品销宝- 明星店铺":
            weeklywb['上周回顾'].cell(46+2,2).value = item[2]#访客数
            weeklywb['上周回顾'].cell(47+2,2).value = item[3]
            weeklywb['上周回顾'].cell(47+2,2).font = excelFont(color=returncolor(item[3]))
            weeklywb['上周回顾'].cell(46+2,3).value = item[10]#支付金额
            weeklywb['上周回顾'].cell(47+2,3).value = item[11]
            weeklywb['上周回顾'].cell(47+2,3).font = excelFont(color=returncolor(item[11]))
            weeklywb['上周回顾'].cell(46+2,4).value = item[12]#支付买家数
            weeklywb['上周回顾'].cell(47+2,4).value = item[13]
            weeklywb['上周回顾'].cell(47+2,4).font = excelFont(color=returncolor(item[13]))
            weeklywb['上周回顾'].cell(46+2,5).value = item[14]#支付转化率
            weeklywb['上周回顾'].cell(47+2,5).value = item[15]
            weeklywb['上周回顾'].cell(47+2,5).font = excelFont(color=returncolor(item[15]))
            weeklywb['上周回顾'].cell(46+2,6).value = item[16]#客单价
            weeklywb['上周回顾'].cell(47+2,6).value = item[17]
            weeklywb['上周回顾'].cell(47+2,6).font = excelFont(color=returncolor(item[17]))
            # weeklywb['上周回顾'].cell(46+2,7).value = item[2]#roi
            # weeklywb['上周回顾'].cell(47+2,7).value = item[3]
        if item[0]=="付费流量" and item[1] == "AI智能投放":
            weeklywb['上周回顾'].cell(48+2,2).value = item[2]#访客数
            weeklywb['上周回顾'].cell(49+2,2).value = item[3]
            weeklywb['上周回顾'].cell(49+2,2).font = excelFont(color=returncolor(item[3]))
            weeklywb['上周回顾'].cell(48+2,3).value = item[10]#支付金额
            weeklywb['上周回顾'].cell(49+2,3).value = item[11]
            weeklywb['上周回顾'].cell(49+2,3).font = excelFont(color=returncolor(item[11]))
            weeklywb['上周回顾'].cell(48+2,4).value = item[12]#支付买家数
            weeklywb['上周回顾'].cell(49+2,4).value = item[13]
            weeklywb['上周回顾'].cell(49+2,4).font = excelFont(color=returncolor(item[13]))
            weeklywb['上周回顾'].cell(48+2,5).value = item[14]#支付转化率
            weeklywb['上周回顾'].cell(49+2,5).value = item[15]
            weeklywb['上周回顾'].cell(49+2,5).font = excelFont(color=returncolor(item[15]))
            weeklywb['上周回顾'].cell(48+2,6).value = item[16]#客单价
            weeklywb['上周回顾'].cell(49+2,6).value = item[17]
            weeklywb['上周回顾'].cell(49+2,6).font = excelFont(color=returncolor(item[17]))
            # weeklywb['上周回顾'].cell(48+2,7).value = item[2]#roi
            # weeklywb['上周回顾'].cell(49+2,7).value = item[3]
        if item[0]=="自主访问" and item[1] == "汇总":
            weeklywb['上周回顾'].cell(50+2,2).value = item[2]#访客数
            weeklywb['上周回顾'].cell(51+2,2).value = item[3]
            weeklywb['上周回顾'].cell(51+2,2).font = excelFont(color=returncolor(item[3]))
            weeklywb['上周回顾'].cell(50+2,3).value = item[10]#支付金额
            weeklywb['上周回顾'].cell(51+2,3).value = item[11]
            weeklywb['上周回顾'].cell(51+2,3).font = excelFont(color=returncolor(item[11]))
            weeklywb['上周回顾'].cell(50+2,4).value = item[12]#支付买家数
            weeklywb['上周回顾'].cell(51+2,4).value = item[13]
            weeklywb['上周回顾'].cell(51+2,4).font = excelFont(color=returncolor(item[13]))
            weeklywb['上周回顾'].cell(50+2,5).value = item[14]#支付转化率
            weeklywb['上周回顾'].cell(51+2,5).value = item[15]
            weeklywb['上周回顾'].cell(51+2,5).font = excelFont(color=returncolor(item[15]))
            weeklywb['上周回顾'].cell(50+2,6).value = item[16]#客单价
            weeklywb['上周回顾'].cell(51+2,6).value = item[17]
            weeklywb['上周回顾'].cell(51+2,6).font = excelFont(color=returncolor(item[17]))
            # weeklywb['上周回顾'].cell(50+2,7).value = item[2]#roi
            # weeklywb['上周回顾'].cell(51+2,7).value = item[3]
        if item[0]=="自主访问" and item[1] == "我的淘宝":
            weeklywb['上周回顾'].cell(52+2,2).value = item[2]#访客数
            weeklywb['上周回顾'].cell(53+2,2).value = item[3]
            weeklywb['上周回顾'].cell(53+2,2).font = excelFont(color=returncolor(item[3]))
            weeklywb['上周回顾'].cell(52+2,3).value = item[10]#支付金额
            weeklywb['上周回顾'].cell(53+2,3).value = item[11]
            weeklywb['上周回顾'].cell(53+2,3).font = excelFont(color=returncolor(item[11]))
            weeklywb['上周回顾'].cell(52+2,4).value = item[12]#支付买家数
            weeklywb['上周回顾'].cell(53+2,4).value = item[13]
            weeklywb['上周回顾'].cell(53+2,4).font = excelFont(color=returncolor(item[13]))
            weeklywb['上周回顾'].cell(52+2,5).value = item[14]#支付转化率
            weeklywb['上周回顾'].cell(53+2,5).value = item[15]
            weeklywb['上周回顾'].cell(53+2,5).font = excelFont(color=returncolor(item[15]))
            weeklywb['上周回顾'].cell(52+2,6).value = item[16]#客单价
            weeklywb['上周回顾'].cell(53+2,6).value = item[17]
            weeklywb['上周回顾'].cell(53+2,6).font = excelFont(color=returncolor(item[17]))
            # weeklywb['上周回顾'].cell(52+2,7).value = item[2]#roi
            # weeklywb['上周回顾'].cell(53+2,7).value = item[3]
        if item[0]=="自主访问" and item[1] == "购物车":
            weeklywb['上周回顾'].cell(54+2,2).value = item[2]#访客数
            weeklywb['上周回顾'].cell(55+2,2).value = item[3]
            weeklywb['上周回顾'].cell(55+2,2).font = excelFont(color=returncolor(item[3]))
            weeklywb['上周回顾'].cell(54+2,3).value = item[10]#支付金额
            weeklywb['上周回顾'].cell(55+2,3).value = item[11]
            weeklywb['上周回顾'].cell(55+2,3).font = excelFont(color=returncolor(item[11]))
            weeklywb['上周回顾'].cell(54+2,4).value = item[12]#支付买家数
            weeklywb['上周回顾'].cell(55+2,4).value = item[13]
            weeklywb['上周回顾'].cell(55+2,4).font = excelFont(color=returncolor(item[13]))
            weeklywb['上周回顾'].cell(54+2,5).value = item[14]#支付转化率
            weeklywb['上周回顾'].cell(55+2,5).value = item[15]
            weeklywb['上周回顾'].cell(55+2,5).font = excelFont(color=returncolor(item[15]))
            weeklywb['上周回顾'].cell(54+2,6).value = item[16]#客单价
            weeklywb['上周回顾'].cell(55+2,6).value = item[17]
            weeklywb['上周回顾'].cell(55+2,6).font = excelFont(color=returncolor(item[17]))
            # weeklywb['上周回顾'].cell(54+2,7).value = item[2]#roi
            # weeklywb['上周回顾'].cell(55+2,7).value = item[3]
        if item[0]=="自主访问" and item[1] == "直接访问":
            weeklywb['上周回顾'].cell(56+2,2).value = item[2]#访客数
            weeklywb['上周回顾'].cell(57+2,2).value = item[3]
            weeklywb['上周回顾'].cell(57+2,2).font = excelFont(color=returncolor(item[3]))
            weeklywb['上周回顾'].cell(56+2,3).value = item[10]#支付金额
            weeklywb['上周回顾'].cell(57+2,3).value = item[11]
            weeklywb['上周回顾'].cell(57+2,3).font = excelFont(color=returncolor(item[11]))
            weeklywb['上周回顾'].cell(56+2,4).value = item[12]#支付买家数
            weeklywb['上周回顾'].cell(57+2,4).value = item[13]
            weeklywb['上周回顾'].cell(57+2,4).font = excelFont(color=returncolor(item[13]))
            weeklywb['上周回顾'].cell(56+2,5).value = item[14]#支付转化率
            weeklywb['上周回顾'].cell(57+2,5).value = item[15]
            weeklywb['上周回顾'].cell(57+2,5).font = excelFont(color=returncolor(item[15]))
            weeklywb['上周回顾'].cell(56+2,6).value = item[16]#客单价
            weeklywb['上周回顾'].cell(57+2,6).value = item[17]
            weeklywb['上周回顾'].cell(57+2,6).font = excelFont(color=returncolor(item[17]))
            # weeklywb['上周回顾'].cell(56+2,7).value = item[2]#roi
            # weeklywb['上周回顾'].cell(57+2,7).value = item[3]
        if item[0]=="大促会场" and item[1] == "汇总":
            weeklywb['上周回顾'].cell(58+2,2).value = item[2]#访客数
            weeklywb['上周回顾'].cell(59+2,2).value = item[3]
            weeklywb['上周回顾'].cell(59+2,2).font = excelFont(color=returncolor(item[3]))
            weeklywb['上周回顾'].cell(58+2,3).value = item[10]#支付金额
            weeklywb['上周回顾'].cell(59+2,3).value = item[11]
            weeklywb['上周回顾'].cell(59+2,3).font = excelFont(color=returncolor(item[11]))
            weeklywb['上周回顾'].cell(58+2,4).value = item[12]#支付买家数
            weeklywb['上周回顾'].cell(59+2,4).value = item[13]
            weeklywb['上周回顾'].cell(59+2,4).font = excelFont(color=returncolor(item[13]))
            weeklywb['上周回顾'].cell(58+2,5).value = item[14]#支付转化率
            weeklywb['上周回顾'].cell(59+2,5).value = item[15]
            weeklywb['上周回顾'].cell(59+2,5).font = excelFont(color=returncolor(item[15]))
            weeklywb['上周回顾'].cell(58+2,6).value = item[16]#客单价
            weeklywb['上周回顾'].cell(59+2,6).value = item[17]
            weeklywb['上周回顾'].cell(59+2,6).font = excelFont(color=returncolor(item[17]))
            # weeklywb['上周回顾'].cell(56+2,7).value = item[2]#roi
            # weeklywb['上周回顾'].cell(57+2,7).value = item[3]

def add_shangpin(bookshangpin):
    bookshangpinsheet = bookshangpin.sheets()[0]
    row =bookshangpinsheet.nrows#总行数
    importdata = bookshangpinsheet
    tempdata = []
    for index,i in enumerate(range(row)):
        tempdata.append(importdata.row_values(i))
    for index,item in enumerate(tempdata):
        if index >5:
            every_weeekly_shangpin_row(item[1],item)
            

def every_weeekly_shangpin_row(id,item):
    for index in range(weeklywb['产品分析'].max_row):
        if id == weeklywb['产品分析'].cell(row = index+1, column = 12).value:
            weeklywb['产品分析'].cell(index+1,2).value=item[6]#商品访客数
            weeklywb['产品分析'].cell(index+1,3).value=item[17]#支付买家数
            weeklywb['产品分析'].cell(index+1,4).value=item[18]#支付件数
            weeklywb['产品分析'].cell(index+1,5).value=item[19]#支付金额
            weeklywb['产品分析'].cell(index+1,6).value=item[20]#支付转化率
            weeklywb['产品分析'].cell(index+1,7).value=item[22]#支付老买家数
            weeklywb['产品分析'].cell(index+1,7).alignment = Alignment(horizontal='right', vertical='center')
            if int(item[17]) != 0 :
                weeklywb['产品分析'].cell(index+1,8).value=(int(item[18])/int(item[17]))#人均支付件数
                weeklywb['产品分析'].cell(index+1,9).value=(int(item[22])/int(item[17]))#老买家占比
        elif id == weeklywb['产品分析'].cell(row = index+1, column = 2).value:
            print(index,"2")


def creat_daily_file():
    wb = Workbook()
    ws1 = wb.active
    ws1.title = "日报"
    ws2 = wb.create_sheet(title="【生意参谋平台】1")
    ws3 = wb.create_sheet(title="竞店")
    wb.save(filename = "./export/太阳谷Tmall项目日报&竞品.xlsx")

def creat_weekly_file():
    wb = Workbook()
    ws1 = wb.active
    ws1.title = "上周回顾"
    ws2 = wb.create_sheet(title="产品分析")
    ws3 = wb.create_sheet(title="_整体看板 统计时间 2021-02-15  2021-02-")
    ws4 = wb.create_sheet(title="Sheet3")
    ws5 = wb.create_sheet(title="无线流量来源")
    ws6 = wb.create_sheet(title="【生意参谋平台】1")
    wb.save(filename = "./export/太阳谷2021年02月15日-02月21日周报及计划更新.xlsx")

def import_file():
    # global bookhongguan
    # global bookjingpin
    for root, dirs, files in os.walk("./in"):
        # print(root) #当前目录路径 
        # print(dirs) #当前路径下所有子目录 
        # print(files) #当前路径下所有非目录子文件
        for file in files:
            # filename = os.path.splitext(file)[0]
            # filetype = os.path.splitext(file)[1]
            if "运营视窗数据" in os.path.splitext(file)[0]:
                if os.path.splitext(file)[1] == ".xls":
                    print("1xls")
                elif os.path.splitext(file)[1] == ".xlsx":
                    bookyunying = xlrd.open_workbook(root + "/" + file)
                    ## bookyunying = load_workbook(root + "/" + file)
                    add_yunying(bookyunying)
                if os.path.splitext(file)[1] == ".csv":
                    print("1csv")
            elif "整体看板" in os.path.splitext(file)[0]:
                if os.path.splitext(file)[1] == ".xls":
                    print("2xls")
                if os.path.splitext(file)[1] == ".xlsx":
                    print("2xlsx")
                if os.path.splitext(file)[1] == ".csv":
                    with open(root + "/" + file,encoding="gbk")as f:
                        zhengticsv = csv.reader(f)
                        add_zhengti(zhengticsv)
                    # print(bookzhenti)
            elif "无线店铺流量来源" in os.path.splitext(file)[0]:
                if os.path.splitext(file)[1] == ".xls":
                    bookwuxian = xlrd.open_workbook(root + "/" + file)
                    ## bookwuxian = load_workbook(root + "/" + file)
                    add_wuxian(bookwuxian)
                    pass
                if os.path.splitext(file)[1] == ".xlsx":
                    print("3xlsx")
                if os.path.splitext(file)[1] == ".csv":
                    print("3csv")
            elif "商品_所有终端" in os.path.splitext(file)[0]:
                if os.path.splitext(file)[1] == ".xls":
                    bookshangpin = xlrd.open_workbook(root + "/" + file)
                    add_shangpin(bookshangpin)
                if os.path.splitext(file)[1] == ".xlsx":
                    bookshangpin = xlrd.open_workbook(root + "/" + file)
                    add_shangpin(bookshangpin)
                    pass
                if os.path.splitext(file)[1] == ".csv":
                    print("4csv")
            elif "宏观监控-核心指标监控" in os.path.splitext(file)[0]:
                if os.path.splitext(file)[1] == ".xls":
                    bookhongguan = xlrd.open_workbook(root + "/" + file)
                    ## bookhongguan = load_workbook(root + "/" + file)
                    # add_daily_data(bookhongguan)
                    pass
                if os.path.splitext(file)[1] == ".xlsx":
                    print("5xlsx")
                if os.path.splitext(file)[1] == ".csv":
                    print("5csv")
            elif "竞店分析 全店 所有终端" in os.path.splitext(file)[0]:
                if os.path.splitext(file)[1] == ".xls":
                    print("6xls")
                if os.path.splitext(file)[1] == ".xlsx":
                    bookjingpin = xlrd.open_workbook(root + "/" + file)
                    # add_jingpin(bookjingpin)
                    pass
                if os.path.splitext(file)[1] == ".csv":
                    print("6csv")

# def copy_daily_file():
#     wbdaily = load_workbook('D:\work\yibide\in\太阳谷Tmall项目日报&竞品.xlsx')
#     wbdaily.copy_worksheet(wbs5)

# def copy_weekly_file():


# def export_daily_file():
#     dailybook = xlrd.open_workbook("./export/太阳谷Tmall项目日报&竞品.xlsx")
#     print(dailybook.sheet_by_index(1),book5.sheet_by_index(1))
    # WorksheetCopy(source_worksheet=book5.sheet_by_index(1), target_worksheet=dailybook.sheet_by_index(0))



def formatdate(date):
    return datetime.strptime(date, "%Y-%m-%d")

def formatdate1(date):
    return datetime.strptime(date, "%Y-%m-%d %H:%M:%S")

def formatyunyingdata(data):
    if "-" in data:
        return ["-" + data.split("-")[1],"008000"]
    elif "+" in data:
        return [data.split("+")[1],"FF0000"]

def returncolor(data):
    if "-" in data:
        return "008000"
    else:
        return "FF0000"
            
if __name__ == '__main__':# creat_daily_file()
    read_export_file()
    import_file()
    # # add_daily_data()
    # # creat_weekly_file()
    # # copy_daily_file()
    # # copy_weekly_file()
    # # export_daily_file()
    # # export_weekly_file()
    # dailywb.save(daily_file_out)
    weeklywb.save(weekly_file_out)
    # dailyPlot()